package common.utils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;

/**
 * Excel表工具
 */
public class PoiExcelUtil {

    public static void main(String[] args) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook("F:\\HeiMaPeiXun\\资料\\SaaS案例\\SaaS-Export\\day09\\03-资料\\poi资料\\demo.xlsx");
        XSSFSheet sheet = workbook.getSheetAt(0);

        Object object = null;
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            for (Cell cell : row) {
                object = getCellValue(cell);
                System.out.print(object);
                System.out.print("\t\t");
            }
            System.out.print("\n");
        }
    }


    //获取单元格内容
    public static Object getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        Object obj = null;
        switch (cell.getCellType()) {
            case STRING:
                String value = String.valueOf(cell.getStringCellValue());
                value = value.replace(" ", "");
                value = value.replace("\n", "");
                value = value.replace("\t", "");
                obj = value;
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)){
                    obj = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
                }else {
                    obj = cell.getNumericCellValue();
                }
                break;
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case FORMULA:
                try {
                    obj = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    String valueOf = String.valueOf(cell.getNumericCellValue());
                    BigDecimal bd = new BigDecimal(Double.valueOf(valueOf));
                    bd = bd.setScale(2, RoundingMode.HALF_UP);
                    obj = bd;
                }
                break;
            case ERROR:
//                obj = cell.getErrorCellValue();
                obj = null;
                break;
            default:
                break;
        }
        return obj;
    }
}
